const mysql = require('mysql2/promise');

// 数据库配置
const pool = mysql.createPool({
    host: 'localhost', // 数据库主机地址
    user: 'root', // 数据库用户名
    password: '123456', // 数据库密码
    database: 'bookstore', // 数据库名，建议统一
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 创建表的函数
async function createTables() {
    try {
        // 用户表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                role VARCHAR(20) NOT NULL DEFAULT 'user',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        // 科普文章表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS articles (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                content TEXT NOT NULL,
                author_id INT NOT NULL,
                status VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (author_id) REFERENCES users(id)
            )
        `);
        // 图书表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS books (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                author VARCHAR(100) NOT NULL,
                price DECIMAL(10,2) NOT NULL,
                image VARCHAR(255),
                description TEXT,
                inventory INT NOT NULL
            )
        `);
        // 购物车表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS cart (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                book_id INT NOT NULL,
                quantity INT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id),
                FOREIGN KEY (book_id) REFERENCES books(id)
            )
        `);
        // 订单表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS orders (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        `);
        // 订单明细表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS order_items (
                id INT AUTO_INCREMENT PRIMARY KEY,
                order_id INT NOT NULL,
                book_id INT NOT NULL,
                quantity INT NOT NULL,
                FOREIGN KEY (order_id) REFERENCES orders(id),
                FOREIGN KEY (book_id) REFERENCES books(id)
            )
        `);
        // 题库表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS questions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                question TEXT NOT NULL,
                options JSON NOT NULL,
                answer VARCHAR(255) NOT NULL,
                category VARCHAR(50)
            )
        `);
        // 答题记录表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS quiz_records (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                question_id INT NOT NULL,
                user_answer VARCHAR(255) NOT NULL,
                is_correct TINYINT(1) NOT NULL,
                answer_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id),
                FOREIGN KEY (question_id) REFERENCES questions(id)
            )
        `);
        // 用户反馈表
        await pool.execute(`
            CREATE TABLE IF NOT EXISTS feedbacks (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                content TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        `);
        console.log('所有表创建成功');
    } catch (error) {
        console.error('创建表时出错:', error);
        throw error;
    }
}

// 添加初始数据的函数
async function addInitialData() {
    try {
        // 检查图书表是否有数据
        const [booksRows] = await pool.execute('SELECT COUNT(*) as count FROM books');
        if (booksRows[0].count === 0) {
            const initialBooks = [
                {
                    title: "Python编程从入门到实践",
                    author: "Eric Matthes",
                    price: 89.0,
                    image: "https://picsum.photos/200/300?random=1",
                    description: "适合初学者的Python编程入门书籍，涵盖基础知识和项目实践。",
                    inventory: 50
                },
                {
                    title: "计算机网络：自顶向下方法",
                    author: "Andrew S. Tanenbaum",
                    price: 129.0,
                    image: "https://picsum.photos/200/300?random=4",
                    description: "从应用层向下讲解计算机网络原理，适合计算机专业学生和网络工程师。",
                    inventory: 60
                },
                {
                    title: "人工智能：一种现代的方法",
                    author: "Stuart Russell, Peter Norvig",
                    price: 159.0,
                    image: "https://picsum.photos/200/300?random=5",
                    description: "人工智能领域的经典教材，全面介绍人工智能的理论和应用。",
                    inventory: 30
                }
            ];

            const insertQuery = 'INSERT INTO books (title, author, price, image, description, inventory) VALUES (?,?,?,?,?,?)';
            for (const book of initialBooks) {
                await pool.execute(insertQuery, [book.title, book.author, book.price, book.image, book.description, book.inventory]);
            }
            console.log('已添加初始图书数据');
        }

        // 检查用户表是否有管理员账号
        const [adminRows] = await pool.execute('SELECT * FROM users WHERE username = ?', ['admin']);
        if (adminRows.length === 0) {
            const insertUserQuery = 'INSERT INTO users (username, email, password, role) VALUES (?,?,?,?)';
            await pool.execute(insertUserQuery, ['admin', 'admin@example.com', 'admin123', 'admin']);
            console.log('已添加管理员账号');
        }
    } catch (error) {
        console.error('添加初始数据时出错:', error);
        throw error;
    }
}

// 初始化数据库
async function initDB() {
    try {
        await createTables();
        await addInitialData();
        console.log('数据库初始化完成');
    } catch (error) {
        console.error('数据库初始化失败:', error);
        throw error;
    }
}

// 导出数据库连接池和初始化函数
module.exports = {
    pool,
    initDB
};